﻿using HCQ2_Common.SQL;
using HCQ2_Model.VRDD.InputDto._0818;
using HCQ2_Model.VRDD.OutputDto._0818;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDCircleCourseRelevanceDAL
    {
        /// <summary>
        ///  获取店铺关联课程列表
        /// </summary>
        /// <param name="store_id">店铺id</param>
        /// <param name="keyword">关键字</param>
        /// <param name="page">第几页</param>
        /// <param name="rows">每页数量</param>
        /// <param name="total">返回总数</param>
        /// <returns></returns>
        public List<OutputCircleRel> GetCircleRelList(int store_id, string keyword, int page, int rows, out int total)
        {
            sb?.Clear();
            sb.Append($@"(SELECT folder_id,folder_order,folder_name,pro_type,group_name,is_recommend,folder_path,folder_note,
            CONVERT(varchar(100),create_time,120)AS create_time,create_name,CONVERT(varchar(100),update_date,120)AS update_date,
            update_name FROM dbo.T_DocumentFolder WHERE 1=1  ");
            if (!string.IsNullOrEmpty(keyword))
                sb.Append($" AND (folder_name LIKE '%{keyword}%' OR folder_path LIKE '{keyword}%' OR group_name LIKE '%{keyword}%') ");
            sb.Append($@" )f INNER JOIN (SELECT id,course_id FROM T_VRDDCircleCourseRelevance WHERE store_id={store_id})r ON f.folder_id=r.course_id ");
            total = HCQ2_Common.Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0)AS num FROM " + sb.ToString()));
            string str = $@"SELECT TOP {rows} * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY folder_order ASC) AS num FROM {sb.ToString()})c WHERE c.num>{(page - 1) * rows}";
            return SqlHelper.ExecuteList<OutputCircleRel>(str);
        }
        /// <summary>
        ///  删除店铺、课程关联
        /// </summary>
        /// <param name="store_id"></param>
        /// <param name="course_id"></param>
        /// <returns></returns>
        public int DeleteCircleRel( string course_id)
        {
            sb?.Clear();
            sb.Append($"DELETE FROM T_VRDDCircleCourseRelevance WHERE id IN({course_id});");
            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }
        /// <summary>
        ///  获取添加关联时的课程列表
        /// </summary>
        /// <param name="store_id">店铺id</param>
        /// <param name="keyword">关键字</param>
        /// <param name="page">第几页</param>
        /// <param name="rows">每页数量</param>
        /// <param name="total">返回总数</param>
        /// <returns></returns>
        public List<OutputCircleRel> GetAddCircleRelList(int store_id, string keyword, int page, int rows, out int total)
        {
            sb?.Clear();
            sb.Append($@" (SELECT folder_id AS  id,folder_order,folder_name,pro_type,group_name,is_recommend,folder_path,folder_note,
            CONVERT(varchar(100),create_time,120)AS create_time,create_name,CONVERT(varchar(100),update_date,120)AS update_date,
            update_name,ROW_NUMBER() OVER(ORDER BY folder_order ASC) AS num FROM dbo.T_DocumentFolder WHERE page_type='VRWorks'
            AND folder_id NOT IN(SELECT course_id FROM T_VRDDCircleCourseRelevance WHERE store_id={store_id})  ");
            if (!string.IsNullOrEmpty(keyword))
                sb.Append($" AND (folder_name LIKE '%{keyword}%' OR folder_path LIKE '{keyword}%' OR group_name LIKE '%{keyword}%') ");
            sb.Append(" )f ");
            total = HCQ2_Common.Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0)AS num FROM " + sb.ToString()));
            string str = $@"SELECT TOP {rows} * FROM {sb.ToString()} WHERE f.num>{(page - 1) * rows}";
            return SqlHelper.ExecuteList<OutputCircleRel>(str);
        }

        /// <summary>
        ///  获取店铺关联节点
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<StoreCourseResult> GetStoreCourseList(InputStoreId param)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM 
            (SELECT course_id FROM T_VRDDCircleCourseRelevance WHERE store_id={param.store_id})r INNER JOIN
            (SELECT folder_id AS ID,folder_name AS Name,folder_path AS Path,folder_note AS Note,folder_pid,
            folder_url AS Icon,is_recommend AS isPush,focus_num AS focusNum,star_num AS starNum,folder_order,
            isFocus=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.T_VRUserFolder WHERE folder_id=f.folder_id AND user_guid='{param.userid}'),
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_VLineUserFolderStar WHERE folder_id=f.folder_id AND user_guid='{param.userid}')
             FROM dbo.T_DocumentFolder f WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            sb.Append(")d ON r.course_id=d.ID;");
            return SqlHelper.ExecuteList<StoreCourseResult>(sb.ToString());
        }
    }
}